create  table jms_dm.dm_tms_shipment_line_load_rate_dt
(
     shipment_no                         varchar(255)  comment '任务单号'
    ,shipment_name                       varchar(255)  comment '线路名称'
    ,date_time                           date          comment '运行日期'
    ,vehicleline_code                    varchar(255)  comment '线路编码'
    ,vehicleline_name                    varchar(255) comment '参考车线名称'
    ,linepart_name                       varchar(255)  comment '线路段'
    ,mileage                             varchar(255)  comment '任务单公里数'
    ,linepart_mileage                    decimal(10,2) comment '线路段公里数'
    ,business_attribute                  varchar(255)  comment '任务类型 1正班2加班'
    ,has_out                             varchar(255)  comment '是否兜底1是2否'
    ,strategy_line                       varchar(255)  comment '否战略线路1是2否'
    ,operation_model                     varchar(255)  comment '运行模式 1单边2对开3往返'
    ,plate_number                        varchar(255)  comment '车牌号'
    ,vehicletype_id                      varchar(255)  comment '车型ID'
    ,vehicletype_name                    varchar(255)  comment '车型名称'
    ,axle_number                         int    comment '车辆轴数'
    ,shipno_planned_departure_time       datetime      comment '任务单规划发车时间'
    ,shipno_actual_departure_time        datetime      comment '任务单实际发车时间'
    ,shipno_planned_arrival_time         datetime      comment '任务单规划到车时间'
    ,shipno_actual_arrival_time          datetime      comment '任务单实际到车时间'
    ,linepart_planned_departure_time     datetime      comment '线路段规划发车时间'
    ,linepart_actual_departure_time      datetime      comment '线路段实际发车时间'
    ,linepart_planned_arrival_time       datetime      comment '线路段规划到车时间'
    ,linepart_actual_arrival_time        datetime      comment '线路段实际到车时间'
    ,start_site_code                     varchar(255)  comment '始发站点编号'
    ,start_site_name                     varchar(255)  comment '始发站点名称'
    ,start_site_sort_num                 varchar(255)  comment '始发站点顺序编号'
    ,end_site_code                       varchar(255)  comment '站点发件下一站编码'
    ,end_site_name                       varchar(255)  comment '站点发件下一站名称'
    ,end_site_sort_num                   varchar(255)  comment '目的站点顺序编号'
    ,start_code                          varchar(255)  comment '始发中心编号'
    ,start_name                          varchar(255)  comment '始发中心名称'
    ,end_code                            varchar(255)  comment '目的中心编号'
    ,end_name                            varchar(255)  comment '目的中心名称'

    ,linepart_count                      bigint        comment '装载票数'
    ,plan_count                          bigint        comment '核载票数'
    ,linepart_rate                       decimal(15,2) comment '线路段装载率'
    ,line_count                          bigint        comment '线路装载票数'
    ,first_site_plan_linepart_count      bigint        comment '首站核载票数'
    ,line_rate                           decimal(15,2) comment '线路装载率'

    ,linepart_weight                     decimal(15,2) comment '装载重量'
    ,plan_weight                         decimal(15,2) comment '核载重量'
    ,linepart_weight_rate                decimal(15,2) comment '线路段重量装载率'
    ,line_weight                         decimal(15,2) comment '线路装载重量'
    ,first_site_plan_linepart_weight     decimal(15,2) comment '首站核载重量'
    ,line_weight_rate                    decimal(15,2) comment '线路重量装载率'

    ,linepart_volume                     decimal(15,4) comment '装载体积'
    ,plan_volume                         decimal(15,4) comment '核载体积'
    ,linepart_volume_rate                decimal(15,2) comment '线路段体积装载率'
    ,line_volume                         decimal(15,4) comment '线路装载体积'
    ,first_site_plan_linepart_volume     decimal(15,4) comment '首站核载体积'
    ,line_volume_rate                    decimal(15,2) comment '线路体积装载率'

    ,start_province_id                   varchar(255) comment '始发省份ID'
    ,start_province_name                 varchar(255) comment '始发省份名称'
    ,start_region_id                     varchar(255) comment '始发大区ID'
    ,start_region_name                   varchar(255) comment '始发大区名称'
    ,start_agent_code                    varchar(255) comment '始发代理区ID'
    ,start_agent_name                    varchar(255) comment '始发代理区名称'
    ,end_province_id                     varchar(255) comment '目的省份ID'
    ,end_province_name                   varchar(255) comment '目的省份名称'
    ,end_region_id                       varchar(255) comment '目的大区ID'
    ,end_region_name                     varchar(255) comment '目的大区名称'
    ,end_agent_code                      varchar(255) comment '目的代理区ID'
    ,end_agent_name                      varchar(255) comment '目的代理区名称'
    ,start_site_province_id              varchar(255) comment '始发站点省份ID'
    ,start_site_province_name            varchar(255) comment '始发站点省份名称'
    ,start_site_region_id                varchar(255) comment '始发站点大区ID'
    ,start_site_region_name              varchar(255) comment '始发站点大区名称'
    ,start_site_agent_code               varchar(255) comment '始发站点代理区ID'
    ,start_site_agent_name               varchar(255) comment '始发站点代理区名称'
    ,end_site_province_id                varchar(255) comment '目的站点省份ID'
    ,end_site_province_name              varchar(255) comment '目的站点省份名称'
    ,end_site_region_id                  varchar(255) comment '目的站点大区ID'
    ,end_site_region_name                varchar(255) comment '目的站点大区名称'
    ,end_site_agent_code                 varchar(255) comment '目的站点代理区ID'
    ,end_site_agent_name                 varchar(255) comment '目的站点代理区名称'
    ,weight0_count                       bigint       comment '重量段0数量(w=0kg)'
    ,weight1_count                       bigint       comment '重量段1数量(0<w<=30kg)'
    ,weight2_count                       bigint       comment '重量段2数量(30<w<1000kg)'
    ,weight3_count                       bigint       comment '重量段3数量(w>=1000kg)'
    ,line_type                           int          comment '线路类型1短驳2直发3经停'
    ,error_scan_flag                     int          comment '错扫标识0无错扫1有错扫'
    ,error_scan_count                    bigint       comment '错扫票数'
    ,cycle_bag_cnt                       bigint       comment '循环袋数量'
    ,cycle_bag_weight                    bigint       comment '循环袋重量'
    ,issupplies                          int          comment '是否物资线路1是0否'
    ,should_cycle_bag_cnt                bigint       comment '应装循环袋数量'
) ENGINE=OLAP
DUPLICATE KEY(`shipment_no`)
COMMENT "干线线路装载率离线"
PARTITION BY RANGE(`date_time`)(
    START ("2024-01-01") END ("2024-01-30") EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(`shipment_no`) BUCKETS 1
PROPERTIES (
    "replication_num" = "3",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.time_zone" = "Asia/Shanghai",
    "dynamic_partition.start" = "-180",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "1",
    "in_memory" = "false",
    "storage_format" = "V2",
    "enable_persistent_index" = "false"
);



